package com.nykredit.kundeservice.data;
import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;
public abstract class DopeDBConnection {
public enum TransactionIsolationLevel {READ_UNCOMMITTED(1),
READ_COMMITTED(2),
REPEATABLE_READ(4),
SERIALIZABLE(8);
private int isolationLevelCode;
public int getIsolationLevelCode(){
return this.isolationLevelCode;
}
private TransactionIsolationLevel(int isolationLevelCode){
this.isolationLevelCode = isolationLevelCode;
}
}
private Connection conn = null;
private String userName;
private String password;
private String driverType;
private String serverName;
private int portNumber;
private String databaseName;
public boolean isClosed() throws DopeDBException{
try {
return this.conn.isClosed();
} catch (SQLException e) {
throw new DopeDBException(e, "Couldnt read connection status", "Der skete en fejl i forbindelse med afl�sning af status p� forbindelse til databasen.");
}
}
public boolean isTransactionDeclared() throws DopeDBException{
try {
if (this.conn.getTransactionIsolation() == Connection.TRANSACTION_NONE)
return false;
else
return true;
} catch (SQLException e) {
throw new DopeDBException(e, "Could not get transaction isolation from connection", "");
}
}
protected DopeDBConnection(String userName, String password, String driverType, String serverName, int portNumber, String databaseName) throws DopeDBException {
this.userName = userName;
this.password = password;
this.driverType = driverType;
this.serverName = serverName;
this.portNumber = portNumber;
this.databaseName = databaseName;
this.openConnection();
}
public void openConnection() throws DopeDBException {
try {
OracleDataSource dataSource = new OracleDataSource();
dataSource.setUser(this.userName);
dataSource.setPassword(this.password);
dataSource.setDriverType(this.driverType);
dataSource.setServerName(this.serverName);
dataSource.setPortNumber(this.portNumber);
dataSource.setDatabaseName(this.databaseName);
this.conn = dataSource.getConnection();
this.conn.createStatement().execute("alter session set NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS'");
dataSource.close();
} catch (SQLException e) {
throw new DopeDBException(e, "Failed to created new OracleDataSource", "Kunne ikke forbinde til database.");
}
}
public void closeConnection() throws DopeDBException{
try {
if (this.conn != null && !this.conn.isClosed())
this.conn.close();
} catch (SQLException e) {
throw new DopeDBException(e, "Could not close the connection to " + this.serverName, "Der skete en fejl ved fors�g p� at lukke forbindelsen til databasen.");
}
}
public ResultSet executeQuery(String query) throws DopeDBException{
ResultSet returnSet = null;
try {
Statement st = conn.createStatement();
returnSet = st.executeQuery(query);
} catch (SQLException e) {
throw new DopeDBException(e, "Error executing query: " + query, "Fejl under foresp�rgsel.");
}
return returnSet;
}
public ResultSet executeInsertStatement(String insert, String[] returnColumns) throws DopeDBException{
try {
PreparedStatement prepStatement;
prepStatement = this.conn.prepareStatement(insert, returnColumns);
prepStatement.executeUpdate();
return prepStatement.getGeneratedKeys();
} catch (SQLException e) {
throw new DopeDBException(e, "Error executing insert: " + insert, "Fejl ved inds�tning af data i database.");
}
}
public int executeUpdateStatement(String update) throws DopeDBException{
try {
Statement st = this.conn.createStatement();
return st.executeUpdate(update);
} catch (SQLException e) {
throw new DopeDBException(e, "Error executing update: " + update, "Fejl ved opdatering af data i dasebase.");
}
}
public int executeDeleteStatement(String delete) throws DopeDBException{
try {
Statement st = conn.createStatement();
return st.executeUpdate(delete);
} catch(SQLException e){
throw new DopeDBException(e, "Error executing delete: " + delete, "Fejl under slettelse af data i database.");
}
}
public void beginTransaction(TransactionIsolationLevel isolationLevel) throws DopeDBException{
try {
this.conn.setAutoCommit(false);
this.conn.setTransactionIsolation(isolationLevel.getIsolationLevelCode());
} catch (SQLException e) {
throw new DopeDBException(e, "Error beginning transaction.", "Fejl ved start af transaktion.");
}
}
public void commitTransaction() throws DopeDBException{
try {
this.conn.commit();
} catch (SQLException e) {
throw new DopeDBException(e, "Error commiting transation.", "Fejl ved gennemf�rsel af transaktion.");
}
}
public void rollbackTransaction() throws DopeDBException{
try {
this.conn.rollback();
} catch (SQLException e) {
throw new DopeDBException(e, "Error rolling back transaction.", "Fejl ved rollback af transaktion.");
}
}
}